import pymysql
import pymysql.cursors
from config.config import db_config
from typing import Optional, List, Any

class DBUtils:
    """
    数据库工具类，提供数据库连接和操作方法
    """

    def __init__(self):
        # 初始化数据库连接参数
        self.connection: Optional[pymysql.connections.Connection] = None
        self._conn_params = dict(
            host=db_config.DB_HOST,
            port=db_config.DB_PORT,
            user=db_config.DB_USER,
            password=db_config.DB_PASSWORD,
            database=db_config.DB_NAME,
            cursorclass=pymysql.cursors.DictCursor
        )

    def connect(self) -> pymysql.connections.Connection:
        """
        建立数据库连接（如已连接则复用）
        """
        if self.connection is None or not self.connection.open:
            try:
                self.connection = pymysql.connect(**self._conn_params)
            except Exception as e:
                raise Exception(f"数据库连接失败: {e}")
        return self.connection

    def execute_query(self, sql: str, params: Optional[Any] = None) -> List[dict]:
        """
        执行查询语句，返回结果列表
        :param sql: 查询SQL语句
        :param params: 查询参数
        :return: 查询结果
        """
        connection = self.connect()
        try:
            with connection.cursor() as cursor:
                cursor.execute(sql, params)
                return cursor.fetchall()
        except Exception as e:
            raise Exception(f"查询执行失败: {e}")

    def execute_update(self, sql: str, params: Optional[Any] = None) -> int:
        """
        执行更新/插入/删除语句，返回影响行数
        :param sql: SQL语句
        :param params: 参数
        :return: 影响行数
        """
        connection = self.connect()
        try:
            with connection.cursor() as cursor:
                affected_rows = cursor.execute(sql, params)
                connection.commit()
                return affected_rows
        except Exception as e:
            connection.rollback()
            raise Exception(f"更新执行失败: {e}")

    def execute_many(self, sql: str, params_list: List[Any]) -> int:
        """
        批量执行SQL语句
        :param sql: SQL语句
        :param params_list: 参数列表
        :return: 影响行数
        """
        connection = self.connect()
        try:
            with connection.cursor() as cursor:
                affected_rows = cursor.executemany(sql, params_list)
                connection.commit()
                return affected_rows
        except Exception as e:
            connection.rollback()
            raise Exception(f"批量执行失败: {e}")

    def close(self):
        """
        关闭数据库连接
        """
        if self.connection and self.connection.open:
            self.connection.close()
            self.connection = None

    def __enter__(self):
        """
        支持with语句上下文管理
        """
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """
        退出with语句时自动关闭连接
        """
        self.close()


# 创建全局实例
db_utils = DBUtils()